const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

async function initGemSystem() {
    let connection;

    try {
        console.log('🔮 开始初始化宝石系统...');

        // 连接数据库
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 读取SQL文件内容
        const fs = require('fs');
        const path = require('path');

        // 创建宝石基础表
        console.log('📋 创建宝石基础表...');
        const gemBaseSql = fs.readFileSync(path.join(__dirname, 'database/schemas/gem_base.sql'), 'utf8');

        // 分离CREATE TABLE和INSERT语句
        const statements = gemBaseSql.split(';').filter(stmt => stmt.trim());

        // 执行CREATE TABLE语句
        try {
            await connection.execute(statements[0] + ';');
            console.log('✅ 宝石基础表创建成功');
        } catch (error) {
            if (error.code === 'ER_TABLE_EXISTS_ERROR') {
                console.log('ℹ️ 宝石基础表已存在，跳过创建');
            } else {
                throw error;
            }
        }

        // 执行INSERT语句
        if (statements.length > 1) {
            try {
                await connection.execute(statements[1] + ';');
                console.log('✅ 宝石基础数据插入成功');
            } catch (error) {
                if (error.code === 'ER_DUP_ENTRY') {
                    console.log('ℹ️ 宝石基础数据已存在，跳过插入');
                } else {
                    throw error;
                }
            }
        }

        // 创建角色宝石背包表
        console.log('📋 创建角色宝石背包表...');
        const gemInventorySql = fs.readFileSync(path.join(__dirname, 'database/schemas/character_gem_inventory.sql'), 'utf8');
        try {
            await connection.execute(gemInventorySql);
            console.log('✅ 角色宝石背包表创建成功');
        } catch (error) {
            if (error.code === 'ER_TABLE_EXISTS_ERROR') {
                console.log('ℹ️ 角色宝石背包表已存在，跳过创建');
            } else {
                throw error;
            }
        }

        // 创建装备宝石镶嵌表
        console.log('📋 创建装备宝石镶嵌表...');
        const gemSocketSql = fs.readFileSync(path.join(__dirname, 'database/schemas/equipment_gem_socket.sql'), 'utf8');
        try {
            await connection.execute(gemSocketSql);
            console.log('✅ 装备宝石镶嵌表创建成功');
        } catch (error) {
            if (error.code === 'ER_TABLE_EXISTS_ERROR') {
                console.log('ℹ️ 装备宝石镶嵌表已存在，跳过创建');
            } else {
                throw error;
            }
        }

        // 创建宝石开矿表
        console.log('📋 创建宝石开矿表...');
        const gemMiningSql = fs.readFileSync(path.join(__dirname, 'database/schemas/gem_mining.sql'), 'utf8');

        // 分离CREATE TABLE和INSERT语句
        const miningStatements = gemMiningSql.split(';').filter(stmt => stmt.trim());

        // 执行CREATE TABLE语句
        try {
            await connection.execute(miningStatements[0] + ';');
            console.log('✅ 宝石开矿表创建成功');
        } catch (error) {
            if (error.code === 'ER_TABLE_EXISTS_ERROR') {
                console.log('ℹ️ 宝石开矿表已存在，跳过创建');
            } else {
                throw error;
            }
        }

        // 执行INSERT语句
        if (miningStatements.length > 1) {
            try {
                await connection.execute(miningStatements[1] + ';');
                console.log('✅ 宝石开矿数据插入成功');
            } catch (error) {
                if (error.code === 'ER_DUP_ENTRY') {
                    console.log('ℹ️ 宝石开矿数据已存在，跳过插入');
                } else {
                    throw error;
                }
            }
        }

        // 验证表是否创建成功
        console.log('🔍 验证表创建结果...');
        const [tables] = await connection.execute(`
      SELECT TABLE_NAME 
      FROM information_schema.TABLES 
      WHERE TABLE_SCHEMA = 'nextjs' 
      AND TABLE_NAME IN ('gem_base', 'character_gem_inventory', 'equipment_gem_socket', 'gem_mining')
    `);

        console.log('📊 已创建的表:');
        tables.forEach(table => {
            console.log(`  - ${table.TABLE_NAME}`);
        });

        // 检查宝石基础数据
        const [gemCount] = await connection.execute('SELECT COUNT(*) as count FROM gem_base');
        console.log(`💎 宝石基础数据: ${gemCount[0].count} 条记录`);

        // 检查开矿配置数据
        const [miningCount] = await connection.execute('SELECT COUNT(*) as count FROM gem_mining');
        console.log(`⛏️ 开矿配置数据: ${miningCount[0].count} 条记录`);

        console.log('\n🎉 宝石系统初始化完成！');
        console.log('\n📋 系统功能说明:');
        console.log('  - 6034: 查询角色宝石背包');
        console.log('  - 2032: 开矿生成宝石 (ID: 154原木, 155精钢, 156珍奇)');
        console.log('  - 6035: 镶嵌宝石到装备');
        console.log('  - 6036: 卸下装备上的宝石');
        console.log('  - 6037: 查询装备宝石镶嵌信息');
        console.log('\n💎 宝石类型:');
        console.log('  1.钻石 2.水晶 3.玛瑙 4.白玉 5.琥珀');
        console.log('  6.翡翠 7.珍珠 8.绿宝石 9.红宝石 10.蓝宝石');
        console.log('  11.猫眼石 12.金刚玉 13.龙血石 14.黑曜石 15.乾坤珠');

    } catch (error) {
        console.error('❌ 初始化失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行初始化
initGemSystem().catch(console.error);
